Alternatives to Apache::DBI?

Hello,

I'm wondering what techniques folks are using to get persistent database
connections other than Apache::DBI.

Apache::DBI has worked great, but I've treated it as a black box and so
don't fully understand what it is doing in the box. That was fine, but
the fact that the current version (1.07) on CPAN is broken and has been
for a year suggests to me that people are using other methods. These
methods might be better than what I've been doing.

I've read about connect caching and pooling, but I'm not sure if this is
what I want to do instead or where best to read up about it.

Thanks!

Kurt Hansen

Re: Alternatives to Apache::DBI?

--000325554c9e9203a10474f68180
Content-Type: text/plain; charset=ISO-8859-1

I run 1.07 on my webserver (algebra.com) and Apache::DBI works great for me.

i

On Fri, Oct 2, 2009 at 11:30 AM, Kurt Hansen wrote:

> Hello,
>
> I'm wondering what techniques folks are using to get persistent database
> connections other than Apache::DBI.
>
> Apache::DBI has worked great, but I've treated it as a black box and so
> don't fully understand what it is doing in the box. That was fine, but the
> fact that the current version (1.07) on CPAN is broken and has been for a
> year suggests to me that people are using other methods. These methods might
> be better than what I've been doing.
>
> I've read about connect caching and pooling, but I'm not sure if this is
> what I want to do instead or where best to read up about it.
>
> Thanks!
>
> Kurt Hansen
>

--000325554c9e9203a10474f68180
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I run 1.07 on my webserver ()=
and Apache::DBI works great for me.

i

Re: Alternatives to Apache::DBI?

Apache::DBI is fine. You can also use DBI->connect_cached, as long as
you are careful to avoid making any database connections during server
startup and install your own cleanup handler to issue a rollback on
all open database handles at the end of every web request.

The Apache::DBI code is very short and simple, so if you're wondering
what it does, just read it.

- Perrin

On Fri, Oct 2, 2009 at 12:30 PM, Kurt Hansen wrote:
> Hello,
>
> I'm wondering what techniques folks are using to get persistent database
> connections other than Apache::DBI.
>
> Apache::DBI has worked great, but I've treated it as a black box and so
> don't fully understand what it is doing in the box. That was fine, but the
> fact that the current version (1.07) on CPAN is broken and has been for a
> year suggests to me that people are using other methods. These methods might
> be better than what I've been doing.
>
> I've read about connect caching and pooling, but I'm not sure if this is
> what I want to do instead or where best to read up about it.
>
> Thanks!
>
> Kurt Hansen
>

Re: Alternatives to Apache::DBI?

On Oct 2, 2009, at 9:30 AM, Kurt Hansen wrote:

> I'm wondering what techniques folks are using to get persistent
> database connections other than Apache::DBI.

I plan to release a new module, DBIx::Connection, on Monday. It's
based on the connection caching stuff in DBIx::Class, and also has
some nice transaction management stuff, so you might want to [check it
out](http://github.com/theory/dbix-connection/).

Right now, following DBIx::Class's precedent, it disables Apache::DBI
when it connects to the database. That may or may not be something I
want to revisit, but since DBIx::Connection does its own caching, I
rather suspect it's best not to also cache with Apache::DBI.

If you use it, you might also want "install your own cleanup handler
to issue a rollback on all open database handles at the end of every
web request," as Perrin says. Bricolage does that with its use of
connect_cached(), and that works great. It's safe to create a
connection on startup with DBIx::Connection though, as it is careful
not to cache across fork or thread boundaries.

Critiques welcome, BTW. I'll likely blog about it next week, too.

Best,

David

Re: Alternatives to Apache::DBI?

On Fri, Oct 2, 2009 at 10:40 PM, David E. Wheeler wrote:
> It's safe to create a connection on
> startup with DBIx::Connection though, as it is careful not to cache across
> fork or thread boundaries.

It may be necessary to set InactiveDestroy on any handles you open
during startup, even if you avoid ever using them again. They will
eventually time out and may cause trouble when the database tries to
clean them up.

- Perrin

Re: Alternatives to Apache::DBI?

On Oct 3, 2009, at 5:25 AM, Perrin Harkins wrote:

>> It's safe to create a connection on
>> startup with DBIx::Connection though, as it is careful not to cache
>> across
>> fork or thread boundaries.
>
> It may be necessary to set InactiveDestroy on any handles you open
> during startup, even if you avoid ever using them again. They will
> eventually time out and may cause trouble when the database tries to
> clean them up.

I realized, reading this, that I should check for the Apache startup
and not cache things if it's during startup. That's useful under
mod_perl, and won't hurt anything elsewhere. I'll get that committed
this weekend.

But yes, when DBIx::Connection detects that it's in a new process and
expires the cached handle, it first sets InactiveDestroy to true. So
we should be good there.

Best,

David

Re: Alternatives to Apache::DBI?

On Sat, Oct 3, 2009 at 4:56 PM, David E. Wheeler wrote:
> I realized, reading this, that I should check for the Apache startup and not
> cache things if it's during startup. That's useful under mod_perl, and won't
> hurt anything elsewhere. I'll get that committed this weekend.

That should work. Or you could have it close all connections at the
end of startup.

- Perrin

Re: Alternatives to Apache::DBI?

On Oct 3, 2009, at 2:00 PM, Perrin Harkins wrote:

>> I realized, reading this, that I should check for the Apache
>> startup and not
>> cache things if it's during startup. That's useful under mod_perl,
>> and won't
>> hurt anything elsewhere. I'll get that committed this weekend.
>
> That should work. Or you could have it close all connections at the
> end of startup.

In PerlPostConfigHandler? Don't see anything like that in mod_perl1.
My mod_perl foo is getting rusty, I gotta admit.

David

Re: Alternatives to Apache::DBI?

--000feaf3209560dce704751f2db1
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Oct 2, 2009 at 7:40 PM, David E. Wheeler wrote:

> On Oct 2, 2009, at 9:30 AM, Kurt Hansen wrote:
>
> I'm wondering what techniques folks are using to get persistent database
>> connections other than Apache::DBI.
>>
>
> I plan to release a new module, DBIx::Connection, on Monday. It's based on
> the connection caching stuff in DBIx::Class, and also has some nice
> transaction management stuff, so you might want to [check it out](
> http://github.com/theory/dbix-connection/).
>

Hi David,

This looks nice. Thanks. I don't use Apache::DBI, but this will be good
for general connection and transaction support. I simply use connect_cached
now with { privite_pid = $$ } but that doesn't allow me to set
InactiveDestroy (although I'm not using $dbh in Apache parent or forking
children so has not been an issue).

One question regarding do():

> It's only if the code reference dies that C will check the connection.
> If the handle is not connected to the database (because the database was
> restarted, for example), I C will create a new database handle and
> execute the code reference again.
>
>
I'm not quite sure how to handle the pings. I also don't like the idea of a
ping every time a $dbh is needed. But, I wonder about re-issuing the
query.

If a do() throws an exception and then the connection is tested and fails
the ping are you sure that the query didn't complete? My concern, of
course, is running the query successfully twice.

At one time I considered inspecting the error message and trying to
determine if it came from the database so that I knew the query failed, and
then reconnect and rerun the query, otherwise just die.

Obviously, you can't reissue the query if in a transaction -- and I see you
check for this.

Checking the ping before returning $dbh isn't fool proof either. The
connection could always die between the ping and then when $dbh is used.

Is the history of the ping to catch connections that have been inactive for
a long period and perhaps timed out? I've thought about only issuing the
ping if the $dbh hasn't been fetched in some amount of time (say a few
minutes) to effectively disable the pings when the site is busy (which
should be most of the time).



If you use it, you might also want "install your own cleanup handler to
> issue a rollback on all open database handles at the end of every web
> request," as Perrin says. Bricolage does that with its use of
> connect_cached(), and that works great. It's safe to create a connection on
> startup with DBIx::Connection though, as it is careful not to cache across
> fork or thread boundaries.
>

But, if you use txn_do() for all transactions then is there any need for the
cleanup handler rollback?

I also see in your disconnect method that you manually issue a rollback,
call disconnect and then undefine $dbh. Doesn't DBI do that automatically
when $dbh is DESTROYed?




--
Bill Moseley
moseley@hank.org

--000feaf3209560dce704751f2db1
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Re: Alternatives to Apache::DBI?

On Sun, Oct 4, 2009 at 10:17 AM, Bill Moseley wrote:
>
> On Fri, Oct 2, 2009 at 7:40 PM, David E. Wheeler
> Is the history of the ping to catch connections that have been inactive f=
or
> a long period and perhaps timed out?=A0 I've thought about only issuing t=
he
> ping if the $dbh hasn't been fetched in some amount of time (say a few
> minutes) to effectively disable the pings when the site is busy (which
> should be most of the time).

I've thought about this also, but I don't have any data to indicate
what that period should be.

I use the ping setting with every request. If the database isn't
available, I want to know immediately, and I'm willing to foot the
overhead of an additional very simple query per each application query
I run.

I would submit as an additional consideration, that the times when the
site is busy are periods where you would want active pings. The
database connections are likely to fail when the site is busy than
when it is idle, I would postulate.

Re: Alternatives to Apache::DBI?

On Oct 4, 2009, at 10:17 AM, Bill Moseley wrote:

> This looks nice. Thanks. I don't use Apache::DBI, but this will be
> good
> for general connection and transaction support. I simply use
> connect_cached
> now with { privite_pid = $$ } but that doesn't allow me to set
> InactiveDestroy (although I'm not using $dbh in Apache parent or
> forking
> children so has not been an issue).

Yeah, in that case, unless you want the transaction management or the
saving of the overhead of ping(), as long as you're not doing anything
in the parent process, I think what you've got is just fine. Very
simple.

> I'm not quite sure how to handle the pings. I also don't like the
> idea of a
> ping every time a $dbh is needed. But, I wonder about re-issuing the
> query.

Yes, you only want to do this if re-executing the code reference has
no side-effects.

> If a do() throws an exception and then the connection is tested and
> fails
> the ping are you sure that the query didn't complete? My concern, of
> course, is running the query successfully twice.

I stole this code from DBIx::Class, which as you likely know, is
pretty widely used. This was their approach, and I think it works well
for them. But even better is to use txn_do() instead, as it should
avoid the possibility of executing the query twice.

> At one time I considered inspecting the error message and trying to
> determine if it came from the database so that I knew the query
> failed, and
> then reconnect and rerun the query, otherwise just die.
>
> Obviously, you can't reissue the query if in a transaction -- and I
> see you
> check for this.

Yep.

> Checking the ping before returning $dbh isn't fool proof either. The
> connection could always die between the ping and then when $dbh is
> used.

Yet this is how the vast majority of database caching approaches work,
including Apache::DBI and connect_cached(). I don't think that's worth
worrying about, frankly.

> Is the history of the ping to catch connections that have been
> inactive for
> a long period and perhaps timed out? I've thought about only
> issuing the
> ping if the $dbh hasn't been fetched in some amount of time (say a few
> minutes) to effectively disable the pings when the site is busy (which
> should be most of the time).

I think that would be more complicated; you'd have to do more record-
keeping. And then what do you do when you don't check it but a
reconnect would allow you to continue?

> But, if you use txn_do() for all transactions then is there any need
> for the
> cleanup handler rollback?

No.

> I also see in your disconnect method that you manually issue a
> rollback,
> call disconnect and then undefine $dbh. Doesn't DBI do that
> automatically
> when $dbh is DESTROYed?

Probably, unless InactiveDestroy is set. That's more code I borrowed
from DBIx::Class.

Best,

David

Plack

--Apple-Mail-1-281824472
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit


Has anyone here played with Plack yet ? ( http://plackperl.org/ )

It's about a week old or so publicly, but I'm sure a few of you folks
here were privvy to a preview...



// Jonathan Vanasco

e. jonathan@2xlp.com
w. http://findmeon.com/user/jvanasco
blog. http://destructuring.net

| - - - - - - - - - -
| Founder/CEO - FindMeOn, Inc.
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| - - - - - - - - - -
| CTO - ArtWeLove, LLC
| ArtWeLove.com - Explore Art On Your Own Terms
| - - - - - - - - - -
| RoadSound.com - Tools for Bands, Stuff for Fans
| - - - - - - - - - -


--Apple-Mail-1-281824472
Content-Type: text/html;
charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

-webkit-line-break: after-white-space; ">

Re: Plack

Whaddaya know...

Ironically, this might have saved Plone at my workplace had I known that
this was on the way. We were looking at writing custom WSGI components
in Python and shuddering (well, I was shuddering)

Jonathan Vanasco wrote:
>
> Has anyone here played with Plack yet ? ( http://plackperl.org/ )
>
> It's about a week old or so publicly, but I'm sure a few of you folks
> here were privvy to a preview...
>
>
>
> // Jonathan Vanasco
>
> e. jonathan@2xlp.com
> w. http://findmeon.com/user/jvanasco
> blog. http://destructuring.net
>
> | - - - - - - - - - -
> | Founder/CEO - FindMeOn, Inc.
> | FindMeOn.com - The cure for Multiple Web Personality Disorder
> | - - - - - - - - - -
> | CTO - ArtWeLove, LLC
> | ArtWeLove.com - Explore Art On Your Own Terms
> | - - - - - - - - - -
> | RoadSound.com - Tools for Bands, Stuff for Fans
> | - - - - - - - - - -
>

Re: Plack

On Oct 15, 2009, at 10:01 AM, Issac Goldstand wrote:

> Whaddaya know...
>
> Ironically, this might have saved Plone at my workplace had I known
> that
> this was on the way. We were looking at writing custom WSGI
> components
> in Python and shuddering (well, I was shuddering)

I'm 80% Python now, so that would be exiciting to me ;) I can't stand
plone though, I do everything in Pylons... which is actually a lot
like ModPerl

Porting WSGI to Perl is really awesome though. And MP is already
supported out of the box !

Re: Plack

Jonathan Vanasco wrote:
>
> Has anyone here played with Plack yet ? ( http://plackperl.org/ )
>
> It's about a week old or so publicly, but I'm sure a few of you folks
> here were privvy to a preview...
>

I haven't played with it, but i have read a bunch of Miyagawa's blog
posts about it. I do know that Jeff Horwitz is planning to support WSGI
in mod_parrot / mod_perl 6, but i don't know exactly what that means ;)

Adam

Re: Plack

On Oct 15, 2009, at 1:36 PM, Adam Prime wrote:

> I haven't played with it, but i have read a bunch of Miyagawa's blog
> posts about it. I do know that Jeff Horwitz is planning to support
> WSGI in mod_parrot / mod_perl 6, but i don't know exactly what that
> means ;)

Yeah I heard about that too. Unfortunately, I seriously doubt I'll
ever use Perl6.

The PSGI spec is really neat. It's just a perl version of WSGI. I'm
hoping to play around with it on some spare time next month, and see
if it can get around some of the weird stuff I've had to do with
libapreq in the past.

Re: Plack

I've been - in my spare time - trying to figure this PSGI thing out. I'm
a Windows guy you see, and I realised there's no PPM for Plack.

Is Strawberry the only alternative?

Jonathan Vanasco wrote:
>
> On Oct 15, 2009, at 1:36 PM, Adam Prime wrote:
>
>> I haven't played with it, but i have read a bunch of Miyagawa's blog
>> posts about it. I do know that Jeff Horwitz is planning to support
>> WSGI in mod_parrot / mod_perl 6, but i don't know exactly what that
>> means ;)
>
> Yeah I heard about that too. Unfortunately, I seriously doubt I'll
> ever use Perl6.
>
> The PSGI spec is really neat. It's just a perl version of WSGI. I'm
> hoping to play around with it on some spare time next month, and see
> if it can get around some of the weird stuff I've had to do with
> libapreq in the past.